using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.IO;
using System.Xml;

public partial class JSONwriter : MyPage
{
    protected SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["MainDB"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        /*SqlCommand sp = new SqlCommand("cm_geocity", conn);
        sp.CommandType = CommandType.StoredProcedure;
            sp.Parameters.AddWithValue("@ct_id", 71);
        conn.Open();
        SqlDataReader rdr = sp.ExecuteReader();

        TextWriter tw = new StreamWriter(Server.MapPath("zonedata/uk_england_london_merton.js"));

        rdr.Read();

        tw.Write(String.Format("geo={{id:{0},city:\"{1}\",lng:{2},lat:{3},zoom:{4},areas:[", rdr.GetInt32(0), rdr.GetString(1), rdr.GetDecimal(2), rdr.GetDecimal(3), rdr.GetInt32(4)));

        rdr.NextResult();

        bool i = false;

        while (rdr.Read())
        {
            if (i)
            {
                tw.Write(",");
            }
            tw.Write(String.Format("{{id:{0},city:\"{1}\",poly:[[{2}]]}}", rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2).Trim().Replace(" ", "],[")));
            i = true;
            //Response.Write(rdr.GetString(1));
        }
        tw.Write("]};");

        rdr.Close();
        conn.Close();
        tw.Close();

        report.Text = "done!";*/

    }

    protected void GeneralLocations(object sender, EventArgs e)
    {
        int parent_id = 81;

        SqlCommand sp = new SqlCommand("cm_citiesTree", conn);
        sp.CommandType = CommandType.StoredProcedure;
        sp.Parameters.AddWithValue("@ct_parent", parent_id);
        conn.Open();
        SqlDataReader rdr = sp.ExecuteReader();

        TextWriter tw = new StreamWriter(Server.MapPath("zonedata/cities.js"));

        bool i = false;

        tw.Write("locs=[");

        while (rdr.Read())
        {
            if (i) tw.Write(",");

            report.Text = String.Format("{0}<br>{1}", report.Text, rdr.GetString(1));

            tw.Write(String.Format("[{0},\"{1}\",{2},{3},\"{4}\",{5},{6},{7},{8},{9}]"
                , rdr.GetInt32(0)
                , rdr.GetString(1)
                , rdr.GetInt32(2)
                , rdr.GetInt32(3)
                , rdr.GetString(4)
                , rdr.GetDecimal(5)
                , rdr.GetDecimal(6)
                , rdr.GetInt32(7)
                , rdr.GetInt32(8)
                , rdr.GetInt16(9)
            ));

            report.Text = String.Format("{0}<br>{1}: {2}", report.Text, rdr.GetString(1), LocJS(rdr.GetInt32(0), rdr.GetString(4) + "/" + rdr.GetString(1)));

            i = true;
        }

        tw.Write("];");

        rdr.Close();
        conn.Close();
        tw.Close();
    }

    protected string LocJS(int areaID, string fileName)
    {
        SqlCommand sp2 = new SqlCommand("SELECT ct_name, ct_long, ct_lat, ct_zoom FROM cm_cities WHERE ct_id = " + areaID.ToString(), conn2);
        sp2.CommandType = CommandType.Text;
            sp2.Parameters.AddWithValue("@ct_id", areaID);
        conn2.Open();
        SqlDataReader rdr2 = sp2.ExecuteReader();

        string jsFile = "zonedata/" + fileName.Substring(1).Replace(" ", "-").Replace("/", "_") + ".js";

        TextWriter tw2 = new StreamWriter(Server.MapPath(jsFile));

        rdr2.Read();

        tw2.Write(String.Format("geo={{id:{0},city:\"{1}\",lng:{2},lat:{3},zoom:{4},path:\"{5}\"}};", areaID, rdr2.GetString(0), rdr2.GetDecimal(1), rdr2.GetDecimal(2), rdr2.GetInt32(3), fileName));

        rdr2.Close();
        conn2.Close();
        tw2.Close();

        return jsFile;
    }


}
